package zhuzx.generator;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.zhuzx.util.GeneralUtil;
import org.zhuzx.util.JdbcUtil;
import org.zhuzx.util.MicroUtil;

public class SqlTextParser {
	
	/** 大小写敏感开关，除非必要（如需要区分字段名的大小写），不需要开启。<br>
	 *  oracle默认存储、输出字段名全大写，这种情况必须关闭此开关。mysql可以输出大小写的字段名。*/
	private static final boolean CASE_SENSITIVITY = false;
	
	/** sql函数翻译 */
	public static final Map<String,String> functions;
	
	static {
		Map<String,String> originFuns = GeneralUtil.loadPropsFileFromClassDirectory(
				SqlTextParser.class, "SqlFunction.properties");
		functions = new HashMap<>();
		for (Map.Entry<String,String> entry : originFuns.entrySet()) {
			functions.put(entry.getKey().trim().toLowerCase(), entry.getValue().trim());
		}
	}
	
	/**
	 * @return 由字段信息组成的二维数组，每个子数组对应一个字段。
	 *         子数组中有三个元素，依次为：字段名，类型，注释。
	 */
	public static String[][] getColumns(String sql) {
		Connection conn = JdbcUtil.getDbConn(null);
		try {
			return getColumns(conn, sql);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			JdbcUtil.close(conn);
		}
	}

	/**
	 * 列别名使用as，表别名不使用as。<br>
	 * 关于不使用as的列别名解析，需要收集更多的样例才能决定，初步设想的表达式：[a-zA-Z0-9_)`'\"]\\s+[a-zA-Z0-9_]+$<br>
	 * 至少有oracle支持将end作为列别名使用，所以无法区分子项中结尾的end是一个别名，还是不带别名的case……when……end句式的结尾。<br>
	 * 不给不同的表取相同的别名（虽然子查询中定义的别名是独立的）。<br>
	 * 查询结果的列名不带表前缀，所以无法区分这个字段来自于哪个表。<br>
	 * @return 由字段信息组成的二维数组，每个子数组对应一个字段。
	 *         子数组中有三个元素，依次为：字段名，类型，注释。
	 */
	@SuppressWarnings("deprecation")
	public static String[][] getColumns(Connection conn, String sql) throws SQLException {
		sql = CASE_SENSITIVITY ? sql : sql.toLowerCase();
		
		String sqlBackUp = sql;
		String sqlLow = sql.toLowerCase();
		int[] fromIndexs = MicroUtil.findMatches(sqlLow, "\\sfrom[\\s(]");
		int[] whereIndexs = MicroUtil.findMatches(sqlLow, "\\swhere[\\s(]");
		int from = fromIndexs[0] + " from ".length();
		int where = (whereIndexs.length == 0) ? sqlLow.length() : whereIndexs[whereIndexs.length-1];
		int lastFrom = fromIndexs[fromIndexs.length-1];
		where = (where < lastFrom) ? sqlLow.length() : where;
		String fromClauseLow = sqlLow.substring(from, where);
		String[] words = fromClauseLow.split("\\s|,");
		Set<String> tableNames = new HashSet<String>(Arrays.asList(words));
		Set<String> allTables = new HashSet<String>();
		ResultSet rs = conn.getMetaData().getTables(
				conn.getCatalog(), null, null, new String[]{"TABLE"});
		while (rs.next()) {
			allTables.add(rs.getString("TABLE_NAME").toLowerCase());
		}
		rs.close();
		tableNames.retainAll(allTables);
		
		Map<String,Map<String,String>> remarks = new HashMap<String,Map<String,String>>();
		for (String tableName : tableNames) {
			String[][] columns = getMetaData(conn, tableName);
			Map<String,String> remark = new HashMap<String,String>();
			for (int i=0; i<columns.length; i++) {
				remark.put(columns[i][0], columns[i][2]);
			}
			remarks.put(tableName, remark);
		}
		
		Map<String,String> fieldRemarks = new LinkedHashMap<String,String>();
		String tableSep = ",|\\bwhere\\b|"
				+ "\\b(inner )?join\\b|"
				+ "\\bleft (outer )?join\\b|\\bright (outer )?join\\b|\\bfull (outer )?join\\b|"
				+ "\\bon\\b|\\busing\\b|"
				+ "\\bnatural\\b|"
				+ "\\bcross join\\b|"
				+ "\\bunion\\b";
		Pattern sepPat = Pattern.compile(tableSep);
		String fromClause = sql.substring(from, where);
		for (String tableName : tableNames) {
			Pattern tablePat = Pattern.compile("(\\s|,|^)" + tableName + "(\\s|,|\\)|$)");
			Matcher tableMat = tablePat.matcher(fromClauseLow);
			while (tableMat.find()) {
				int aliasStart = tableMat.end();
				Matcher sepMat = sepPat.matcher(fromClauseLow);
				int aliasEnd = 0;
				if (sepMat.find(aliasStart - 1)) {
					aliasEnd = sepMat.start();
				} else {
					aliasEnd = fromClauseLow.length();
				}
				String alias = fromClause.substring(aliasStart, aliasEnd).trim();
				if (alias.length() == 0) {
					alias = tableName;
					fieldRemarks.putAll(remarks.get(tableName));
				}
				remarks.put(alias, remarks.get(tableName));
			}
		}
		
		String selectRegex = "(\\s|\\(|^)select[\\s(]";
		String fromRegex = "\\sfrom[\\s(]";
		String asRegex = "[)\\s]as\\s";
		int[] selIndexs = MicroUtil.findMatches(sqlLow, selectRegex);
		for (int i=selIndexs.length-1; i>=0; i--) {
			String subSel = sql.substring(selIndexs[i] + "select ".length(),
					MicroUtil.searchMateByRegex(sqlLow, selectRegex, fromRegex, selIndexs[i]));
			String[] fields = subSel.split(",");
			fields = merge(fields);
			for (String field : fields) {
				field = field.trim();
				int[] asIndex = MicroUtil.findMatches(field.toLowerCase(), asRegex);
				if (asIndex.length == 0) {
					String[] des = describeField(field, remarks, fieldRemarks);
					fieldRemarks.put(des[0], des[1]);
				} else {
					String originField = field.substring(0, asIndex[0]).trim();
					String asField = field.substring(asIndex[0] + " as ".length()).trim();
					String[] des = describeField(originField, remarks, fieldRemarks);
					fieldRemarks.put(asField, des[1]);
				}
			}
			
			StringBuilder sb = new StringBuilder();
			if (fields.length == 1) {
				Map.Entry<String,String> last = null;
				//LinkedHashMap中Entry与单纯的key或者value迭代速度相较？
				for (Map.Entry<String,String> entry : fieldRemarks.entrySet()) {
					last = entry;
				}
				sb.append("_" + i + "_");
				fieldRemarks.put(sb.toString(), last.getValue());
			}
			
			if (i == 0) break;
			int bracketLeft = sql.substring(0, selIndexs[i]+1).lastIndexOf("(");
			int bracketRight = MicroUtil.searchMate(sql, '(', ')', bracketLeft) + 1;
			for (int j=bracketLeft; j<bracketRight-sb.length(); j++) {
				sb.append(" ");
			}
			String spaces = sb.toString();
			sb.setLength(0);
			sb.append(sql);
			sql = sb.replace(bracketLeft, bracketRight, spaces).toString();
			sqlLow = sql.toLowerCase();
		}
		
		Statement stm = conn.createStatement();
		ResultSet resSet = stm.executeQuery(sqlBackUp);
		String[][] briefColumns = getBriefColumns(resSet);
		stm.close();
		String[][] columns = new String[briefColumns.length][3];
		for (int i=0; i<briefColumns.length; i++) {
			columns[i][0] = briefColumns[i][0];
			columns[i][1] = briefColumns[i][1];
			columns[i][2] = fieldRemarks.get(briefColumns[i][0]);
		}
		return columns;
	}
	
	/**
	 * 获取指定表的元数据
	 * @return 由字段信息组成的二维数组，每个子数组对应一个字段。
	 *         子数组中有五个元素，依次为：字段名，类型，注释，长度，小数位精度。
	 */
	public static String[][] getMetaData(Connection conn, String tableName) throws SQLException {
		/*
		 * 从oracle获取的DatabaseMetaData有严重的问题。
		 * 其getColumns方法得到的ResultSet中有重复的列信息（结果集有重复的行）。
		 * 关键是这些重复的列还不是全部的，并不是把所有的列都重复一遍，暂时还未找到规律，哪些列会重复哪些列不会。
		 * 而且重复的那一部分列信息没有注释，推断是jdbc驱动的问题。
		 * 鉴于以上原因，需要通过ResultSetMetaData.getColumnCount()获取字段数量。
		 */
		DatabaseMetaData dmd = conn.getMetaData();
		ResultSet rs = dmd.getColumns(conn.getCatalog(), null, tableName ,null);
		if (!rs.next()) {
			rs.close();
			if (!MicroUtil.isUpperCase(tableName)) {//换大写重试一次
				return getMetaData(conn, tableName.toUpperCase());
			} else {
				throw new RuntimeException("无法获取到" + tableName + "的元数据");
			}
		}
		String sql = "select * from " + tableName + " where 1 = 2";
		Statement stm = conn.createStatement();
		ResultSet resSet = stm.executeQuery(sql);
		String[][] briefColumns = getBriefColumns(resSet);
		stm.close();
		String[][] columns = new String[briefColumns.length][5];
		for (int i=0; i<briefColumns.length; i++) {
			columns[i][0] = briefColumns[i][0];
			columns[i][1] = briefColumns[i][1];
			columns[i][2] = rs.getString("REMARKS");
			columns[i][3] = rs.getString("COLUMN_SIZE");
			columns[i][4] = rs.getString("DECIMAL_DIGITS");
			rs.next();
		}
		rs.close();
		return columns;
	}
	
	/**
	 * 从结果集中获取简单的列信息，以基本类型描述。
	 */
	public static String[][] getBriefColumns(ResultSet rs) throws SQLException {
		ResultSetMetaData rsmd = rs.getMetaData();
		String[][] columns = new String[rsmd.getColumnCount()][2];
		for (int i=0; i<rsmd.getColumnCount(); i++) {
			columns[i][0] = rsmd.getColumnLabel(i+1);
			columns[i][0] = (CASE_SENSITIVITY) ? columns[i][0] : columns[i][0].toLowerCase();
			/* 
			 * ResultSetMetaData.getColumnClassName()方法得到的类型名称是包装类型，而且是全限定类名。
			 * 该方法对于oracle的TIMESTAMP(6)类型，无法获取到对应的Java类型（返回null），很可能还存在其它类似情况。
			 */
			int type = rsmd.getColumnType(i+1);
			String typeName = DatabaseMetadataUtil.types.get(type);
			//对于number类型，任何长度都会得到BigDecimal
			if (type == Types.NUMERIC) {
				int precision = rsmd.getPrecision(i+1);
				int scale = rsmd.getScale(i+1);
				if (scale == 0) {
					if (precision <= 2) {
						typeName = "byte";
					} else if (precision <= 9) {
						typeName = "int";
					} else if (precision <= 18){
						typeName = "long";
					} else {
						typeName = "java.math.BigInteger";
					}
				} else if (scale <= 2) {
					if (precision <= 5) {
						typeName = "float";
					} else if (precision <= 14) {
						typeName = "double";
					}
				}
			}
			columns[i][1] = typeName;
		}
		return columns;
	}
	
	/**
	 * 处理select子元素中存在逗号导致被割裂的情况，合并使其恢复原状。
	 * @param fields select列表，调用此方法不会改变传入的这个数组。
	 * @return 返回新的数组，传入的原数组不变。
	 */
	private static String[] merge(String[] fields) {
		List<String> list = new ArrayList<String>();
		for (String field : fields) {
			String[] brackets = MicroUtil.extractMatches(field, "\\(|\\)");
			int indicator = 0;
			for (String bracket : brackets) {
				if (bracket.equals("(")) indicator++;
				if (bracket.equals(")")) indicator--;
				if (indicator < 0) break;
			}
			if (indicator < 0) {
				String merged = list.get(list.size()-1) + "," + field;
				list.set(list.size()-1, merged);
			} else {
				list.add(field);
			}
		}
		return list.toArray(new String[list.size()]);
	}
	
	/**
	 * 描述一个select子元素
	 * @param field select子元素
	 * @param remarks 所有表的字段的注释，以表别名为键。
	 * @param fieldRemarks 从select子元素（有所处理，可以视为等同结果集中的键）到注释的映射，最终将从这个map中取注释。
	 * @return 仅两个元素，select子元素+对应的注释。
	 */
	private static String[] describeField(String field,
			Map<String,Map<String,String>> remarks, Map<String,String> fieldRemarks) {
		String[] des = new String[2];
		if (GeneralUtil.isBlank(field)) return des;
		String regex = "[\\W&&[^\\s.]]";
		int[] indexs = MicroUtil.findMatches(field, regex);
		if (indexs.length > 0) {
			if (indexs.length == 1 && field.charAt(indexs[0]) == '*') {
				if (field.indexOf(".") > -1) {
					fieldRemarks.putAll(remarks.get(field.split("\\.")[0]));
				} else {
					for (Map<String,String> rem : remarks.values()) {
						fieldRemarks.putAll(rem);
					}
				}
				return des;
			}
			des[0] = field;
			String expression = field.replaceAll("\\s+", " ").toLowerCase();
			String sepRegex = "[\\W&&[^.]]";
			String[] words = expression.split(sepRegex);
			for (String word : words) {
				word = word.trim();
				if (word.length() == 0) continue;
				if (word.indexOf(".") != -1) {
					String[] temp = word.split("\\.");
					Map<String,String> tableRem = remarks.get(temp[0]);
					if (tableRem != null && tableRem.get(temp[1]) != null) {
						word = word.replace(".", "[.]");
						expression = expression.replaceAll(word, tableRem.get(temp[1]));
					}
				} else {
					if (fieldRemarks.get(word) != null) {
						expression = expression.replaceAll(word, fieldRemarks.get(word));
					}
					if (functions.get(word) != null) {
						expression = expression.replaceAll(word, functions.get(word));
					}
				}
			}
			
			Set<String> set = new HashSet<String>(Arrays.asList(words));
			if (set.containsAll(Arrays.asList(new String[]{"case", "when", "then"}))) {
				String[][] caseMapping = new String[][] {
						{"case\\s+when", "若"},
						{"case", "若"},
						{"and", "且"},
						{"or", "或"},
						{"when", "为"},
						{"then", "则取"},
						{"else", "否则取"},
						{"end", ""}
				};
				for (String[] pair : caseMapping) {
					expression = expression.replaceAll("\\b" + pair[0] + "\\b", pair[1]);
				}
			}
			expression = expression.replaceAll(
					functions.get("count") + " ?[(] ?(\\w+[.])?[*] ?[)]", "总数");
			
			des[1] = expression;
		} else {
			if (field.indexOf(".") != -1) {
				String[] temp = field.split("\\.");
				des[0] = temp[1];
				des[1] = remarks.get(temp[0]).get(temp[1]);
			} else {
				des[0] = field;
				des[1] = fieldRemarks.get(field);
			}
		}
		return des;
	}
	


}